-- =============================================
-- Inserts a record to Publish_Message_Log
--
-- Modifications
-- 07/07/2003    E.Tatsui
-- =============================================


CREATE        PROCEDURE amsp_CMInsertPublishMessageLog
  @InPublishRequestID integer,
  @InPublishRequestDetailID integer,
  @InMessageNumber integer,
  @InMessageText varchar(2000),
  @InLocation varchar(100),
  @InPublishServerCode char(1) = NULL
AS
BEGIN
  DECLARE
    @RowNum integer,
    @PublishRequestStatusCode char(1),
    @NumOfAttempts integer,
    @MaxNumOfAttempts integer

  IF @InPublishRequestID = 0 OR @InPublishRequestDetailID = 0
    SET @RowNum = 0
  ELSE
    SELECT @RowNum = Count(*)
      FROM Publish_Message_Log
     WHERE PublishRequestDetailID = @InPublishRequestDetailID
       AND PublishRequestID = @InPublishRequestID

  IF @RowNum = 1
    UPDATE Publish_Message_Log
       SET MessageNumber = @InMessageNumber,
           MessageText = @InMessageText,
           Location = @InLocation,
           DisplayFlag = 'Y',
           MessageDateTime = CURRENT_TIMESTAMP
     WHERE PublishRequestDetailID = @InPublishRequestDetailID
       AND PublishRequestID = @InPublishRequestID

  ELSE
    INSERT INTO Publish_Message_Log
                (PublishRequestID,
                 PublishRequestDetailID,
                 MessageNumber,
                 MessageText,
                 MessageDateTime,
                 Location,
                 PublishServerCode)
    VALUES (@InPublishRequestID,
            @InPublishRequestDetailID,
            @InMessageNumber,
            @InMessageText,
            CURRENT_TIMESTAMP,
            @InLocation,
            @InPublishServerCode)

  IF @InPublishRequestDetailID IS NOT NULL AND @InPublishRequestDetailID <> 0 BEGIN
    UPDATE Publish_Request_Detail
       SET NumOfAttempts = NumOfAttempts + 1
     WHERE PublishRequestDetailID = @InPublishRequestDetailID

    SELECT @MaxNumOfAttempts = Convert(integer,Value)
      FROM System_Variable WITH (NOLOCK)
     WHERE Name = 'CMPublishingMaxNumOfAttempts'

    SELECT @PublishRequestStatusCode = PublishRequestStatusCode,
           @NumOfAttempts = NumOfAttempts
      FROM Publish_Request_Detail WITH (NOLOCK)
     WHERE PublishRequestDetailID = @InPublishRequestDetailID

    IF @PublishRequestStatusCode = 'N' AND @NumOfAttempts >= @MaxNumOfAttempts
      -- Mark this request as failed after the max tries.
      UPDATE Publish_Request_Detail
         SET PublishRequestStatusCode = 'F'
       WHERE PublishRequestDetailID = @InPublishRequestDetailID
  END
  -- If detail id is not specified, see if we can update using the request id.
  ELSE IF @InPublishRequestDetailID = 0 AND @InPublishRequestID <> 0 BEGIN
      UPDATE Publish_Request_Detail
         SET NumOfAttempts = NumOfAttempts + 1,
             PublishRequestStatusCode = CASE WHEN (NumOfAttempts + 1) >= @MaxNumOfAttempts
                                             THEN 'F'
                                        ELSE PublishRequestStatusCode END
       WHERE PublishRequestID = @InPublishRequestID
  END
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMInsertPublishMessageLog] TO [IMIS]
GO